package org.hy.dao.impl;

import org.hy.dao.StaffDao;
import org.hy.entity.Staff;
import org.hy.util.DBUtil;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class StaffDaoImpl4Jdbc implements StaffDao {
    @Override
    public int insert(Staff staff) {
        int r = 0;

        String sql = "insert into staffs(name,age,phone,sta_pos) values(?,?,?,?)";

        Connection conn = DBUtil.getConnection();

        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);

            ps.setString(1,staff.getName());
            ps.setInt(2,staff.getAge());
            ps.setString(3,String.valueOf(staff.getPhone()));
            ps.setString(4,staff.getStaPos());
            r = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("添加失败",e);
        }finally {
            DBUtil.close(null,ps,conn);
        }
            return r;
    }

    @Override
    public int delete(int id) {
        int r = 0;
        String sql = "delete from staffs where id = ?";
        Connection conn = DBUtil.getConnection();

        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            r = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("删除失败",e);
        }finally {
            DBUtil.close(null,ps,conn);
        }
        return r;
    }

    @Override
    public int updateUser(Staff staff) {
        String sql = "update staffs set name = ?,age = ?,phone = ?,sta_pos = ?,update_time where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,staff.getName());
            ps.setInt(2,staff.getAge());
            ps.setString(3,String.valueOf(staff.getPhone()));
            ps.setString(4,staff.getStaPos());
            ps.setTimestamp(5, Timestamp.valueOf(staff.getUpdateTime()));
            ps.setInt(6,staff.getId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.close(null,ps,conn);
        }
    }

    @Override
    public List<Staff> selectByPage(int currentPage, int pageSize) {
        List<Staff> list = new ArrayList<>();

        String sql = "select * from staffs limit ?,?";

        Connection conn = DBUtil.getConnection();

        PreparedStatement ps = null;

        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(sql);

            int limit = (currentPage-1)*pageSize;
            ps.setInt(1,limit);
            ps.setInt(2,pageSize);

            rs = ps.executeQuery();

            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String phone = rs.getString("phone");
                String staPos = rs.getString("staPos");
                LocalDateTime addTime = rs.getTimestamp("addTime").toLocalDateTime();
                LocalDateTime updateTime = rs.getTimestamp("updateTime").toLocalDateTime();

                Staff staff = new Staff(id,name,age,phone,staPos,addTime,updateTime);
                staff.setId(id);
                staff.setName(name);
                staff.setAge(age);
                staff.setPhone(phone);
                staff.setStaPos(staPos);
                staff.setAddTime(addTime);
                staff.setUpdateTime(updateTime);

                list.add(staff);

             }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return list;
    }

    @Override
    public Staff selectById(int id) {

        String sql = "select * from staffs where id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if (rs.next()){
                int staffId = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String phone = rs.getString("phone");
                String staPos = rs.getString("staPos");
                LocalDateTime addTime = rs.getTimestamp("addTime").toLocalDateTime();
                LocalDateTime updateTime = rs.getTimestamp("updateTime").toLocalDateTime();
                return new Staff(staffId,name,age,phone,staPos,addTime,updateTime);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return null;
    }
}
